package henu.dao.impl;

import java.sql.SQLException;
import java.util.List;

import javax.management.Query;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import henu.bean.Seminar;
import henu.dao.SeminarDao;
import henu.dao.factory.DaoFactory;
import henu.util.Dbcp;
public class SeminarDaoImpl implements SeminarDao{

	/*public static void main(String[] args){
		SeminarDaoImpl se = new SeminarDaoImpl();
		Seminar s = new Seminar();
		s.setSeid(123);
		s.setSort("wenzi");
		s.setSubjectname("yuchen");
		s.setSubjecttime("2012-12-12");
		s.setRemark("wode");
		System.out.println(se.save(s));
		
		
	}*/
	@Override
	public int save(Seminar seminar) {
		// TODO Auto-generated method stub
		int result = 0;
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "INSERT INTO SEMINAR(sort,subjectname,subjecttime,remark) VALUES(?,?,?,?)";
		Object[] params = {seminar.getSort(),seminar.getSubjectname(),seminar.getSubjecttime(),seminar.getRemark()};
		try {
			result=runner.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int delete(int seId) {
		// TODO Auto-generated method stub
		int result = 0;
		try {
			QueryRunner runner = DaoFactory.getRunner();
			String sql ="delete from seminar where seId = ?";
			result=runner.update(sql,seId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int update(int seid, Seminar se) {
		// TODO Auto-generated method stub
		int result = 0;
		try {
			QueryRunner runner = DaoFactory.getRunner();
			String sql = "UPDATE seminar SET sort=?,subjectname=?,subjecttime=?,remark=? WHERE seid=?";
			Object[] params = {se.getSort(),se.getSubjectname(),se.getSubjecttime(),se.getRemark(),seid};
			result = runner.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(seid+"asd");
		return result;
	}

	@Override
	public List<Seminar> findAll(String order, String sort, String property,
			String key) {
		List<Seminar> list = null;
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * FROM Seminar where " + property + " = ? "
				+ " ORDER BY  " + order + " " + sort;
		try {
			list = runner.query(sql, new BeanListHandler<Seminar>(Seminar.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Seminar findById(int seid) {
		// TODO Auto-generated method stub
		Seminar se = null;
		String sql ="select * from Seminar where seid=?";
		QueryRunner runner = DaoFactory.getRunner();
		try {
			se = runner.query(sql,new BeanHandler<Seminar>(Seminar.class), seid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return se;
	}

	@Override
	public List<Seminar> findByProperty(String property, String key,
			String order, String sort, int start, int end) {
		// TODO Auto-generated method stub
		List <Seminar> list = null;
		QueryRunner runner = DaoFactory.getRunner();
		String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from seminar where "+property+"='"+key+"'  order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
		try {
			list = runner.query(sql, new BeanListHandler<Seminar>(Seminar.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/*@Override
	public List<Seminar> findByName(String subjectname) {
		// TODO Auto-generated method stub
		QueryRunner runner = DaoFactory.getRunner();
		List<Seminar> list = null;
		String sql = "SELECT * FROM SEMINAR WHERE ";
		return null;
	}*/

	@Override
	//根据专题表列出所有专题
	public List<Seminar> findSubjectName() {
		// TODO Auto-generated method stub
			List<Seminar> list = null;
			QueryRunner runner = DaoFactory.getRunner();
			String sql = "SELECT * FROM Seminar";
			try {
				list = runner.query(sql, new BeanListHandler<Seminar>(Seminar.class));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return list;
	}
	

}
